package dao.impl;

import bean.Course;
import bean.Mathe;
import bean.Sc;
import dao.ScDao;
import util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ScDaoImpl implements ScDao {

    Connection con = null;

    Mathe mathe = null;

    PreparedStatement ps = null;

    ResultSet rs = null;

    int row = 0;

    @Override
    public List<Mathe> GetScAll() {
        List<Mathe> Mathelist = new ArrayList<Mathe>();
        try {
            con = JdbcUtil.getCon();
            String sql = "SELECT \n" +
                    "    s.Sno,\n" +
                    "\t\ts.Sname,\n" +
                    "\t\ts.Ssex,\n" +
                    "\t\ts.Sdept,\n" +
                    "\t\tc.Cno,\n" +
                    "\t\tc.Cname,\n" +
                    "\t\tc.Cpno,\n" +
                    "\t\tc.Credit,\n" +
                    "\t\tsc.Grade\n" +
                    "FROM \n" +
                    "    sc\n" +
                    "INNER JOIN \n" +
                    "    student s ON sc.sno = s.sno\n" +
                    "INNER JOIN \n" +
                    "    course c ON sc.cno = c.cno;";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                mathe = new Mathe();
                mathe.setSno(rs.getString("Sno"));
                mathe.setSname(rs.getString("Sname"));
                mathe.setSsex(rs.getString("Ssex"));
                mathe.setSdept(rs.getString("Sdept"));
                mathe.setCno(rs.getString("Cno"));
                mathe.setCname(rs.getString("Cname"));
                mathe.setCpno(rs.getString("Cpno"));
                mathe.setCredit(rs.getString("Credit"));
                mathe.setGrade(rs.getString("Grade"));
                Mathelist.add(mathe);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return Mathelist;
    }

    @Override
    public Mathe GetScBySno(String Sno) {
        try {
            con = JdbcUtil.getCon();
            String sql = "SELECT \n" +
                    "    s.Sno,\n" +
                    "    s.Sname,\n" +
                    "    s.Ssex,\n" +
                    "    s.Sdept,\n" +
                    "    c.Cno,\n" +
                    "    c.Cname,\n" +
                    "    c.Cpno,\n" +
                    "    c.Credit,\n" +
                    "    sc.Grade\n" +
                    "FROM \n" +
                    "    sc\n" +
                    "INNER JOIN \n" +
                    "    student s ON sc.sno = s.sno\n" +
                    "INNER JOIN \n" +
                    "    course c ON sc.cno = c.cno\n" +
                    "WHERE \n" +
                    "    s.Sno = ?;";
            ps = con.prepareStatement(sql);
            ps.setString(1, Sno);
            rs = ps.executeQuery();
            while (rs.next()){
                mathe = new Mathe();
                mathe.setSno(rs.getString("Sno"));
                mathe.setSname(rs.getString("Sname"));
                mathe.setSsex(rs.getString("Ssex"));
                mathe.setSdept(rs.getString("Sdept"));
                mathe.setCno(rs.getString("Cno"));
                mathe.setCname(rs.getString("Cname"));
                mathe.setCpno(rs.getString("Cpno"));
                mathe.setCredit(rs.getString("Credit"));
                mathe.setGrade(rs.getString("Grade"));
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return mathe;
    }

    @Override
    public int AddSc(Mathe sc) {
        return 0;
    }

    @Override
    public int UpdateSc(Sc sc) {
        try {
            con = JdbcUtil.getCon();
            String sql = "update sc set Cno = ?,Grade = ? where Sno = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, sc.getCno());
            ps.setString(2, String.valueOf(sc.getGrade()));
            ps.setString(3, sc.getSno());
            row = ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row;
    }

    @Override
    public int DeleteSc(String Sno) {
        return 0;
    }

}
